package com.crm.dao.saling;

import com.crm.pojo.Contract;
import com.crm.pojo.Dispatcher;
import com.crm.pojo.DispatcherDetail;
import com.crm.vo.DispatcherContractClientProductVO;
import com.crm.vo.DispatcherDetailProductCODetailVO;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.math.BigDecimal;
import java.util.List;

/**
 * Created by dr5hx on 2018/8/10
 */
public interface DispatcherDetailsDao extends CrudRepository<DispatcherDetail, Long> {
    /**
     * 删除对应发货单的对应发货详情
     *
     * @param dispatcher
     */
    @Query("update DispatcherDetail  d set d.dispatcherDetailsIsDel='是' where d.dispatcher=?1")
    @Modifying
    public void deleteDispatcherDetailsByDispatcherDetailsId(Dispatcher dispatcher);

    /**
     * 显示对应发货单的对应发货详情
     *
     * @param dispatcher
     * @return
     */
    @Query("from DispatcherDetail  d where d.dispatcher=?1 and d.dispatcherDetailsIsDel='否'")
    public List<DispatcherDetail> getDispatcherDetailsList(Dispatcher dispatcher);

    /**
     * 分页显示所有的未删除的发货详情
     *
     * @param pageable
     * @return
     */
    @Query("from DispatcherDetail d where d.dispatcherDetailsIsDel='否'")
    public List<DispatcherDetail> showDispatcherDetails(Pageable pageable);

    /**
     * 显示对应合同的发货单
     *
     * @param contractId
     * @param contract
     * @return
     */
    @Query("select new com.crm.vo.DispatcherDetailProductCODetailVO(dispatcherDetail,p,codetails)  from DispatcherDetail  dispatcherDetail,ProductionDetail  p,CODetail codetails where dispatcherDetail.dispatcher.fkOrderId=?1 and codetails.contract=?2 and dispatcherDetail.dispatcherDetailsIsDel='否' and codetails.cOProductId= dispatcherDetail.pId and p.pid=dispatcherDetail.pId ")
    public List<DispatcherDetailProductCODetailVO> getDispatcherDetailListByContractId(BigDecimal contractId, Contract contract);

    //通过发货单id查询
    @Query("from DispatcherDetail  dd where dd.dispatcherDetailsIsDel='否'and dd.dispatcher.dispatcherId=?1")
    public List<DispatcherDetail> findAllBydid(Long did);

    //通过发货单id查询发货明细数量
    @Query(nativeQuery = true, value = "select count(*) from DISPATCHER_DETAILS dd where dd.DISPATCHER_DETAILS_IS_DEL='否'and dd.DISPATCHER_ID=?1")
    public Long queryCountD(Long did);

    //根据产品id查询已经通过审核的发货明细总数
    @Query(nativeQuery = true, value = "select sum(dd.PRODUCT_ACCOUNT) from DISPATCHER_DETAILS dd join PRODUCTION_DETAILS pd on pd.PID=dd.P_ID where pd.PRODUCTION_DETAILS_IS_DEL='未删除' and dd.P_ID=?1 and dd.FK_DISPATCHER_ID=1")
    public Long queryCountByproid(Long proid);

    /**
     * 查询一个发货细节
     *
     * @return
     */
    @Query("select new com.crm.vo.DispatcherContractClientProductVO(dispatcherDetail,contract,p,clientInfo,codetail) from CODetail  codetail, DispatcherDetail  dispatcherDetail,Dispatcher dispatcher,ProductionDetail p,ClientInfo  clientInfo,Contract  contract where dispatcherDetail.dispatcher=dispatcher and dispatcher.dispatcherIsDel='否' and dispatcherDetail.dispatcherDetailsIsDel='否' and dispatcher.fkOrderId=contract.contractId and contract.fkCustomerId=clientInfo.cid and dispatcherDetail.pId=p.pid and dispatcherDetail.pId=codetail.cOProductId ")
    public List<DispatcherContractClientProductVO> getDispatcherContractClientProductVOList(Pageable pageable);

    /**
     * 统计发货详细细节数量
     *
     * @return
     */
    @Query("select count(dispatcherDetail) from DispatcherDetail  dispatcherDetail where dispatcherDetail.dispatcherDetailsIsDel='否'  ")
    public Long getDispatcherDetailsCount();

    @Query("select dispatcherDetail from DispatcherDetail dispatcherDetail where dispatcherDetail.dispatcher=?1")
    List<DispatcherDetail> getDispatcherDetailListByDispatcherId(Dispatcher dispatcher);
}
